import { Pool } from 'pg'
import { Redis } from 'ioredis'
import { Connection } from '../models/Connection'
import { connectionService } from './connectionService'
import { logger } from '../utils/logger'

export interface TableInfo {
  table_name: string
  table_schema: string
  table_type: string
  table_comment?: string
}

export interface ColumnInfo {
  column_name: string
  data_type: string
  is_nullable: boolean
  column_default?: string
  column_comment?: string
  ordinal_position: number
}

export interface SchemaInfo {
  tables: TableInfo[]
  columns: { [tableName: string]: ColumnInfo[] }
  keywords: string[]
  functions: string[]
}

class SchemaService {
  private redis: Redis
  private cachePrefix = 'schema:'
  private cacheTTL = 3600 // 1小时缓存

  constructor(redis: Redis) {
    this.redis = redis
  }

  /**
   * 获取数据库schema信息
   */
  async getSchemaInfo(connectionId: string, forceRefresh = false): Promise<SchemaInfo> {
    const cacheKey = `${this.cachePrefix}${connectionId}`
    
    try {
      // 尝试从缓存获取
      if (!forceRefresh) {
        const cached = await this.redis.get(cacheKey)
        if (cached) {
          logger.info(`Schema cache hit for connection ${connectionId}`)
          return JSON.parse(cached)
        }
      }

      // 从数据库获取
      const schemaInfo = await this.fetchSchemaFromDatabase(connectionId)
      
      // 缓存结果
      await this.redis.setex(cacheKey, this.cacheTTL, JSON.stringify(schemaInfo))
      logger.info(`Schema cached for connection ${connectionId}`)
      
      return schemaInfo
    } catch (error) {
      logger.error('Failed to get schema info:', error)
      throw error
    }
  }

  /**
   * 从数据库获取schema信息
   */
  private async fetchSchemaFromDatabase(connectionId: string): Promise<SchemaInfo> {
    const connection = await connectionService.getConnection(connectionId)
    if (!connection) {
      throw new Error('Connection not found')
    }

    const pool = await connectionService.getConnectionPool(connectionId)
    
    try {
      // 获取表信息
      const tables = await this.fetchTables(pool)
      
      // 获取列信息
      const columns: { [tableName: string]: ColumnInfo[] } = {}
      for (const table of tables) {
        columns[table.table_name] = await this.fetchColumns(pool, table.table_name, table.table_schema)
      }
      
      // 获取关键字和函数
      const keywords = await this.fetchKeywords(pool)
      const functions = await this.fetchFunctions(pool)
      
      return {
        tables,
        columns,
        keywords,
        functions,
      }
    } finally {
      // 注意：不要关闭连接池，它会被复用
    }
  }

  /**
   * 获取表信息
   */
  private async fetchTables(pool: Pool): Promise<TableInfo[]> {
    const query = `
      SELECT 
        table_name,
        table_schema,
        table_type,
        table_comment
      FROM information_schema.tables 
      WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
      ORDER BY table_schema, table_name
    `
    
    const result = await pool.query(query)
    return result.rows.map(row => ({
      table_name: row.table_name,
      table_schema: row.table_schema,
      table_type: row.table_type,
      table_comment: row.table_comment,
    }))
  }

  /**
   * 获取列信息
   */
  private async fetchColumns(pool: Pool, tableName: string, schemaName: string): Promise<ColumnInfo[]> {
    const query = `
      SELECT 
        column_name,
        data_type,
        is_nullable,
        column_default,
        column_comment,
        ordinal_position
      FROM information_schema.columns 
      WHERE table_name = $1 AND table_schema = $2
      ORDER BY ordinal_position
    `
    
    const result = await pool.query(query, [tableName, schemaName])
    return result.rows.map(row => ({
      column_name: row.column_name,
      data_type: row.data_type,
      is_nullable: row.is_nullable === 'YES',
      column_default: row.column_default,
      column_comment: row.column_comment,
      ordinal_position: parseInt(row.ordinal_position),
    }))
  }

  /**
   * 获取SQL关键字
   */
  private async fetchKeywords(pool: Pool): Promise<string[]> {
    // Doris SQL关键字列表
    const dorisKeywords = [
      'SELECT', 'FROM', 'WHERE', 'GROUP BY', 'ORDER BY', 'HAVING',
      'INSERT', 'UPDATE', 'DELETE', 'CREATE', 'DROP', 'ALTER',
      'TABLE', 'INDEX', 'VIEW', 'DATABASE', 'SCHEMA',
      'JOIN', 'INNER JOIN', 'LEFT JOIN', 'RIGHT JOIN', 'FULL JOIN',
      'UNION', 'UNION ALL', 'INTERSECT', 'EXCEPT',
      'AND', 'OR', 'NOT', 'IN', 'EXISTS', 'BETWEEN', 'LIKE',
      'IS NULL', 'IS NOT NULL',
      'COUNT', 'SUM', 'AVG', 'MIN', 'MAX',
      'DISTINCT', 'ALL', 'AS', 'CASE', 'WHEN', 'THEN', 'ELSE', 'END',
      'IF', 'IFNULL', 'COALESCE', 'NULLIF',
      'CAST', 'CONVERT', 'SUBSTRING', 'LENGTH', 'UPPER', 'LOWER',
      'DATE', 'TIME', 'TIMESTAMP', 'NOW', 'CURDATE', 'CURTIME',
      'YEAR', 'MONTH', 'DAY', 'HOUR', 'MINUTE', 'SECOND',
      'LIMIT', 'OFFSET', 'TOP',
    ]
    
    return dorisKeywords
  }

  /**
   * 获取SQL函数
   */
  private async fetchFunctions(pool: Pool): Promise<string[]> {
    // Doris内置函数列表
    const dorisFunctions = [
      // 聚合函数
      'COUNT', 'SUM', 'AVG', 'MIN', 'MAX', 'STDDEV', 'VARIANCE',
      'GROUP_CONCAT', 'BIT_AND', 'BIT_OR', 'BIT_XOR',
      
      // 字符串函数
      'CONCAT', 'CONCAT_WS', 'SUBSTRING', 'LEFT', 'RIGHT', 'LENGTH',
      'CHAR_LENGTH', 'UPPER', 'LOWER', 'TRIM', 'LTRIM', 'RTRIM',
      'REPLACE', 'REGEXP_REPLACE', 'SPLIT_PART', 'REVERSE',
      
      // 数学函数
      'ABS', 'CEIL', 'FLOOR', 'ROUND', 'TRUNCATE', 'MOD', 'POWER',
      'SQRT', 'EXP', 'LOG', 'LOG10', 'SIN', 'COS', 'TAN',
      'ASIN', 'ACOS', 'ATAN', 'RAND', 'SIGN',
      
      // 日期时间函数
      'NOW', 'CURDATE', 'CURTIME', 'DATE', 'TIME', 'TIMESTAMP',
      'YEAR', 'MONTH', 'DAY', 'HOUR', 'MINUTE', 'SECOND',
      'DAYOFWEEK', 'DAYOFMONTH', 'DAYOFYEAR', 'WEEKOFYEAR',
      'DATE_ADD', 'DATE_SUB', 'DATEDIFF', 'DATE_FORMAT',
      'STR_TO_DATE', 'UNIX_TIMESTAMP', 'FROM_UNIXTIME',
      
      // 条件函数
      'IF', 'IFNULL', 'NULLIF', 'COALESCE', 'CASE',
      
      // 类型转换函数
      'CAST', 'CONVERT', 'FORMAT',
      
      // JSON函数
      'JSON_EXTRACT', 'JSON_UNQUOTE', 'JSON_VALID', 'JSON_ARRAY',
      'JSON_OBJECT', 'JSON_MERGE', 'JSON_SET', 'JSON_INSERT',
      
      // 窗口函数
      'ROW_NUMBER', 'RANK', 'DENSE_RANK', 'NTILE',
      'LAG', 'LEAD', 'FIRST_VALUE', 'LAST_VALUE',
      
      // 其他函数
      'MD5', 'SHA1', 'SHA2', 'CRC32', 'INET_ATON', 'INET_NTOA',
    ]
    
    return dorisFunctions
  }

  /**
   * 搜索表名
   */
  async searchTables(connectionId: string, keyword: string): Promise<TableInfo[]> {
    const schemaInfo = await this.getSchemaInfo(connectionId)
    const lowerKeyword = keyword.toLowerCase()
    
    return schemaInfo.tables.filter(table =>
      table.table_name.toLowerCase().includes(lowerKeyword) ||
      table.table_comment?.toLowerCase().includes(lowerKeyword)
    )
  }

  /**
   * 搜索列名
   */
  async searchColumns(connectionId: string, keyword: string, tableName?: string): Promise<{table: string, columns: ColumnInfo[]}[]> {
    const schemaInfo = await this.getSchemaInfo(connectionId)
    const lowerKeyword = keyword.toLowerCase()
    const results: {table: string, columns: ColumnInfo[]}[] = []
    
    const tablesToSearch = tableName ? [tableName] : Object.keys(schemaInfo.columns)
    
    for (const table of tablesToSearch) {
      const columns = schemaInfo.columns[table] || []
      const matchedColumns = columns.filter(column =>
        column.column_name.toLowerCase().includes(lowerKeyword) ||
        column.column_comment?.toLowerCase().includes(lowerKeyword)
      )
      
      if (matchedColumns.length > 0) {
        results.push({ table, columns: matchedColumns })
      }
    }
    
    return results
  }

  /**
   * 获取表的列信息
   */
  async getTableColumns(connectionId: string, tableName: string): Promise<ColumnInfo[]> {
    const schemaInfo = await this.getSchemaInfo(connectionId)
    return schemaInfo.columns[tableName] || []
  }

  /**
   * 刷新schema缓存
   */
  async refreshSchema(connectionId: string): Promise<SchemaInfo> {
    const cacheKey = `${this.cachePrefix}${connectionId}`
    await this.redis.del(cacheKey)
    return this.getSchemaInfo(connectionId, true)
  }

  /**
   * 清除所有schema缓存
   */
  async clearAllCache(): Promise<void> {
    const keys = await this.redis.keys(`${this.cachePrefix}*`)
    if (keys.length > 0) {
      await this.redis.del(...keys)
    }
    logger.info('All schema cache cleared')
  }

  /**
   * 获取智能提示建议
   */
  async getAutoCompleteSuggestions(
    connectionId: string,
    context: {
      currentWord: string
      previousWords: string[]
      position: { line: number, column: number }
    }
  ): Promise<{
    tables: TableInfo[]
    columns: ColumnInfo[]
    keywords: string[]
    functions: string[]
  }> {
    const schemaInfo = await this.getSchemaInfo(connectionId)
    const { currentWord, previousWords } = context
    const lowerCurrentWord = currentWord.toLowerCase()
    
    // 根据上下文判断建议类型
    const lastWord = previousWords[previousWords.length - 1]?.toLowerCase()
    const secondLastWord = previousWords[previousWords.length - 2]?.toLowerCase()
    
    let tables: TableInfo[] = []
    let columns: ColumnInfo[] = []
    let keywords: string[] = []
    let functions: string[] = []
    
    // 如果在FROM或JOIN后面，建议表名
    if (['from', 'join', 'inner', 'left', 'right', 'full'].includes(lastWord)) {
      tables = schemaInfo.tables.filter(table =>
        table.table_name.toLowerCase().startsWith(lowerCurrentWord)
      )
    }
    // 如果在SELECT后面或者有表名上下文，建议列名
    else if (lastWord === 'select' || this.hasTableContext(previousWords)) {
      // 尝试从上下文中提取表名
      const tableNames = this.extractTableNames(previousWords)
      for (const tableName of tableNames) {
        const tableColumns = schemaInfo.columns[tableName] || []
        columns.push(...tableColumns.filter(col =>
          col.column_name.toLowerCase().startsWith(lowerCurrentWord)
        ))
      }
      
      // 如果没有特定表上下文，建议所有列
      if (columns.length === 0) {
        for (const tableName of Object.keys(schemaInfo.columns)) {
          const tableColumns = schemaInfo.columns[tableName] || []
          columns.push(...tableColumns.filter(col =>
            col.column_name.toLowerCase().startsWith(lowerCurrentWord)
          ))
        }
      }
    }
    
    // 总是包含匹配的关键字和函数
    keywords = schemaInfo.keywords.filter(keyword =>
      keyword.toLowerCase().startsWith(lowerCurrentWord)
    )
    
    functions = schemaInfo.functions.filter(func =>
      func.toLowerCase().startsWith(lowerCurrentWord)
    )
    
    return {
      tables: tables.slice(0, 50), // 限制结果数量
      columns: columns.slice(0, 50),
      keywords: keywords.slice(0, 20),
      functions: functions.slice(0, 30),
    }
  }

  /**
   * 检查是否有表上下文
   */
  private hasTableContext(words: string[]): boolean {
    const lowerWords = words.map(w => w.toLowerCase())
    return lowerWords.includes('from') || lowerWords.includes('join')
  }

  /**
   * 从SQL上下文中提取表名
   */
  private extractTableNames(words: string[]): string[] {
    const tableNames: string[] = []
    const lowerWords = words.map(w => w.toLowerCase())
    
    for (let i = 0; i < lowerWords.length; i++) {
      if (['from', 'join'].includes(lowerWords[i]) && i + 1 < words.length) {
        tableNames.push(words[i + 1])
      }
    }
    
    return [...new Set(tableNames)] // 去重
  }
}

// 创建一个占位符实例，将在应用启动时初始化
let schemaServiceInstance: SchemaService | null = null

export const initializeSchemaService = (redis: Redis) => {
  schemaServiceInstance = new SchemaService(redis)
}

export const schemaService = new Proxy({} as SchemaService, {
  get(target, prop) {
    if (!schemaServiceInstance) {
      throw new Error('SchemaService not initialized. Call initializeSchemaService first.')
    }
    return (schemaServiceInstance as any)[prop]
  }
})